package com.jy.wms.common.base;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExportUtils {
	/**
	 * 设置sheet表头信息
	 *
	 * @param headers
	 * @param title
	 */
	public static HSSFSheet sheet;

	public static void outputHeaders(String[] headers, HSSFWorkbook workbook,
									 String title) {
		sheet = workbook.createSheet(title);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.WHITE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		// 在第一行创建表头
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			// 设置列宽
			sheet.setColumnWidth(i, 4000);
			// 对第一行创建列，对每一列进行赋值
			row.createCell(i).setCellValue(headers[i]);
		}
	}

	/**
	 * 设置每行数据
	 *
	 * @param headers
	 * @param colums
	 * @param workbook
	 * @param rowIndex
	 */
	public static void outputColums(String[] headers, List<?> colums,
                                    HSSFWorkbook workbook, int rowIndex, String title) {

		// 生成并设置另一个样式
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.WHITE.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		HSSFFont font2 = workbook.createFont();

		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);
		HSSFRow row;
		int headerSize = headers.length;
		int columnSize = colums.size();
		// 循环多少行
		for (int i = 0; i < colums.size(); i++) {
			// 从rowIndex行开始创建行
			row = sheet.createRow(rowIndex + i);
			Object obj = colums.get(i);
			// 循环多少列
			for (int j = 0; j < headers.length; j++) {
				Object value = getFieldValueByName(headers[j], obj);
				Boolean isNum = false;// data鏄惁涓烘暟鍊煎瀷
				Boolean isInteger = false;// data鏄惁涓烘暣鏁�
				Boolean isPercent = false;// data鏄惁涓虹櫨鍒嗘暟
				if (value != null || "".equals(value)) {
					// 匹配是否是数字
					isNum = value.toString().matches("^(-?\\d+)(\\.\\d+)?$");
					// 是否是整数
					isInteger = value.toString().matches("^[-\\+]?[\\d]*$");
					// 是否有百分号
					isPercent = value.toString().contains("%");
					if (isNum && !isPercent) {
						HSSFDataFormat df = workbook.createDataFormat();
						if (isInteger) {
							style2.setDataFormat(df.getFormat("#,#0.00"));
						} else {
							style2.setDataFormat(df.getFormat("#,##0.0000"));
						}

						row.createCell(j).setCellStyle(style2);

						row.createCell(j).setCellValue(
								Double.parseDouble(value.toString()));

					} else {

						row.createCell(j).setCellStyle(style2);

						row.createCell(j).setCellValue(value.toString());
					}
				} else if (value == null || "".equals(value)) {
					row.createCell(j).setCellValue("");
				}

			}
		}
	}

	/**
	 * 根据对象的属性获取值
	 *
	 * @param fieldName
	 * @param obj
	 * @return
	 */
	private static Object getFieldValueByName(String fieldName, Object obj) {
		String firstLetter = fieldName.substring(0, 1).toUpperCase();
		String getter = "get" + firstLetter + fieldName.substring(1);
		try {
			Method method = obj.getClass().getMethod(getter, new Class[] {});
			Object value = method.invoke(obj, new Object[] {});
			return value;
		} catch (Exception e) {
			System.out.println("属性不存在");
			return null;
		}

	}

	/**
	 * 读取配置文件获取标题和内容
	 *
	 * @param in
	 * @return
	 */
	public static Map<String, List<String>> getHeadTitle(InputStream in) {
		OrderedProperties prop = new OrderedProperties();
		Map<String, List<String>> map = new HashMap<String, List<String>>();
		List<String> headTitle = new ArrayList<String>();
		List<String> fieldName = new ArrayList<String>();

		try {
			prop.load(in);
			Iterator<String> it = prop.stringPropertyNames().iterator();
			while (it.hasNext()) {
				String key = it.next();
				headTitle.add(key);
				fieldName.add(prop.getProperty(key));
			}
			in.close();
			map.put("headTitle", headTitle);
			map.put("fieldName", fieldName);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} // /加载属性列表
		return map;

	}

	/**
	 * 导出信息创建工作铺
	 * @param headTitle 标题
	 * @param columnName 列属性
	 * @param list 数据
	 * @param title 文件名
	 * @return HSSFWorkbook
	 */
	public static HSSFWorkbook getWorkbook(String[] headTitle, String[] columnName, List<?> list, String title){
		HSSFWorkbook workbook = new HSSFWorkbook();
		ExportUtils.outputHeaders(headTitle,workbook,title);
		//调用工具类生成内容
		ExportUtils.outputColums(columnName, list,workbook,1,title);
		return workbook;
	}

	/**
	 * 导出excel
	 * @param response 响应
	 * @param fileName 文件名
	 * @param workbook 工作铺
	 */
	public static void outPutWorkbook(HttpServletResponse response, String fileName, Workbook workbook){

		try {
			response.setContentType("application/vnd.ms-excel;");
			response.setHeader("Content-Disposition", "attachment;filename=\""
					+ new String(fileName.getBytes("gbk"), "iso-8859-1")
					+ ".xls\"");
			OutputStream outputStream = response.getOutputStream();
			workbook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	/**
	 * excel读写工具类 */
		private final static String xls = "xls";
		private final static String xlsx = "xlsx";

		/**
		 * 读入excel文件，解析后返回
		 * @param file
		 * @throws IOException
		 */
		public static List<String[]> readExcel(MultipartFile file) throws Exception{
			//检查文件
			checkFile(file);
			//获得Workbook工作薄对象
			Workbook workbook = getWorkBook(file);
			//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
			List<String[]> list = new ArrayList<String[]>();
			if(workbook != null){
				for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
					//获得当前sheet工作表
					Sheet sheet = workbook.getSheetAt(sheetNum);
					if(sheet == null){
						continue;
					}
					//获得当前sheet的开始行
					int firstRowNum  = sheet.getFirstRowNum();
					//获得当前sheet的结束行
					int lastRowNum = sheet.getLastRowNum();
					//循环除了第一行的所有行
					for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
						//获得当前行
						Row row = sheet.getRow(rowNum);
						if(row == null){
							continue;
						}
						//获得当前行的开始列
						int firstCellNum = row.getFirstCellNum();
						//获得当前行的列数
						int lastCellNum = row.getPhysicalNumberOfCells();
						String[] cells = new String[row.getPhysicalNumberOfCells()];
						//循环当前行
						for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
							Cell cell = row.getCell(cellNum);
							cells[cellNum] = getCellValue(cell);
						}
						list.add(cells);
					}
				}
				workbook.close();
			}
			return list;
		}
		public static void checkFile(MultipartFile file) throws Exception{
			//判断文件是否存在
			if(null == file){
				throw new ServiceException("文件不存在！");
			}
			//获得文件名
			String fileName = file.getName();
			//判断文件是否是excel文件
			if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
				throw new ServiceException(fileName + "不是excel文件");
			}
		}
		public static Workbook getWorkBook(MultipartFile file) throws ServiceException {
			//获得文件名
			String fileName = file.getOriginalFilename();
			//创建Workbook工作薄对象，表示整个excel
			Workbook workbook = null;
			try {
				//获取excel文件的io流
				InputStream is = file.getInputStream();
				//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
				if(fileName.endsWith(xls)){
					//2003
					workbook = new HSSFWorkbook(is);
				}else if(fileName.endsWith(xlsx)){
					//2007
					workbook = new XSSFWorkbook(is);
				}
			} catch (Exception e) {
				throw new ServiceException(e.getMessage());
			}
			return workbook;
		}
		public static String getCellValue(Cell cell){
			String cellValue = "";
			if(cell == null){
				return cellValue;
			}
			//把数字当成String来读，避免出现1读成1.0的情况
			if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
				cell.setCellType(Cell.CELL_TYPE_STRING);
			}
			//判断数据的类型
			switch (cell.getCellType()){
				case Cell.CELL_TYPE_NUMERIC: //数字
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date d = cell.getDateCellValue();
						DateFormat formater = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
						cellValue = formater.format(d);
					}else{
						cellValue = String.valueOf(cell.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_STRING: //字符串
					cellValue = String.valueOf(cell.getStringCellValue());
					break;
				case Cell.CELL_TYPE_BOOLEAN: //Boolean
					cellValue = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA: //公式
					cellValue = String.valueOf(cell.getCellFormula());
					break;
				case Cell.CELL_TYPE_BLANK: //空值
					cellValue = "";
					break;
				case Cell.CELL_TYPE_ERROR: //故障
					cellValue = "非法字符";
					break;
				default:
					cellValue = "未知类型";
					break;
			}
			return cellValue;
		}
}
